<?php
require_once 'config.php';
class MakeTable
{
    private $db;
    public function __construct ()
    {
        $this->db = mysql_connect(DB_HOST, DB_USER, DB_PWD) or die('Could not connect: ' . mysql_error());
        echo DB_PREFIX . '_' . DB_NAME;
        mysql_select_db(DB_PREFIX . '_' . DB_NAME) or die('Could not select database');
        mysql_query('SET NAMES utf8');
        echo 'Connected successfully';
        echo "\n";
        echo "Please waiting ...\n";
        /*


// 执行 SQL 查询
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// 用 HTML 显示结果


// 释放结果集
*/
    // 关闭连接
    //mysql_close ( $this->db );
    }
    public function refreshTable ()
    {
        $metadataSetIds = $this->refreshTableStruct();
        $this->refreshTableData($metadataSetIds);
    }
    public function refreshTableStruct ()
    {
    	$metadataSetIds = array();
        $str_result = '';
        //查找全部有效的 metadata_set
        $categories = CATEGORY_ID;
        if(empty($categories)){
        	$query = 'SELECT * FROM `metadata_sets` where is_delete = 0';
        }else{
        	$query = "SELECT * FROM `metadata_sets` where is_delete = 0 and id in (select metadata_set_id from categories where id in({$categories}))";
        }

        $result = mysql_query($query) or die('Query failed: ' . mysql_error());
        while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        	$metadataSetIds[] = $line['id'];
            $sql = 'DROP TABLE IF EXISTS auto_metadata_set_' . $line['id'];
            mysql_query($sql);
            $sql = 'CREATE TABLE ';
            $tableName = 'auto_metadata_set_' . $line['id'];
            $sql = $sql . $tableName . ' (';
            $sql = $sql . '`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,';
            $metaQuery = 'select 	metadatas.*
		    			from	metadatas
		    			inner	join metadata_groups
		    			on		metadata_groups.id = metadatas.group_id and metadata_groups.is_delete = 0
		    			where	metadatas.is_delete=0 and metadata_groups.metadata_set_id = ' . $line['id'];
            $metaResult = mysql_query($metaQuery) or die('Query failed: ' . mysql_error());
            $index = array();
            while ($metaLine = mysql_fetch_array($metaResult, MYSQL_ASSOC)) {
                $fieldName = 'auto_metadata_' . $metaLine['id'];
                switch ($metaLine['input_type']) {
                    case 1:
                    case 2:
                    case 3:
                    case 6:
                        $sql = $sql . "`$fieldName` VARCHAR( 255 ) NOT NULL ,";
                        array_push($index, $fieldName);
                        break;
                    case 5:
                        $sql = $sql . "`$fieldName` INT( 11 ) NOT NULL DEFAULT '0',";
                        //array_push($index, $fieldName);
                        break;
                    default:
                        $sql = $sql . "`$fieldName` VARCHAR( 255 ) NOT NULL ,";
                        array_push($index, $fieldName);
                        break; 
                }
            }
            $sql = $sql . '`product_id` INT NOT NULL ,';
            $sql = $sql . '`product_item_id` INT NOT NULL ,';
            $sql = $sql . ' INDEX (product_id,product_item_id)';
//            if(count($index)<17){
//                  $sql = $sql .' , FULLTEXT(' . implode(',', $index) . ')';
//            }
          $sql = $sql .' ) ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;';
            if (! mysql_query($sql)) {
                $str_result .= "<span style='color:#f00;'>{$sql}</span><br />\n";
                echo $str_result;
            } else {    // $this->refreshTableData($line['id']);
            }
            $str_result .= $sql . "<br />\n";
            
           
        }
        mysql_free_result($metaResult);
        mysql_free_result($result);
        return $metadataSetIds;
    }
    /*
    public function refreshTableData ($metadataSetIds)
    {
    	if(empty($metadataSetIds)){
    		exit();
    	}
    	$metadataSetId = implode(",",$metadataSetIds);
        //查找全部有效的 product_item
        $query = "SELECT `product_items`.*, `products`.`metadata_set_id` FROM `product_items` inner join `products` on `products`.id = `product_items`.product_id and `products`.is_delete = 0 where `product_items`.is_delete=0 and `products`.`metadata_set_id` in ({$metadataSetId}) ";
        $result = mysql_query($query) or die('Query failed: ' . mysql_error());
        while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $sql = 'INSERT INTO ';
            $tableName = 'auto_metadata_set_' . $line['metadata_set_id'];
            $sql = $sql . $tableName;
            //按 product_item_id 查找 该 product_item 的全部有效的 item_metadata
            $metaQuery = 'select 	item_metadatas.*,metadatas.input_type
		    			from	item_metadatas
		    			inner	join metadatas on metadatas.id = item_metadatas.metadata_id
		    			and		metadatas.is_delete = 0
		    			where	item_metadatas.is_delete=0 and product_item_id = ' . $line['id'];
            $metaResult = mysql_query($metaQuery) or die('Query failed: ' . mysql_error());
            $key = array();
            $value = array();
             //生成 insert 语句的数据部分 
            while ($metaLine = mysql_fetch_array($metaResult, MYSQL_ASSOC)) {
                $fieldName = 'auto_metadata_' . $metaLine['metadata_id'];
                switch ($metaLine['input_type']) {
                    case 2:
                        array_push($key, '`' . $fieldName . '`');
                        array_push($value, "'" . $metaLine['desc_value'] . "'");
                        break;
                    case 1:
                    case 3:
                    case 6:
                    case 5:
                        array_push($key, '`' . $fieldName . '`');
                        array_push($value, "'" . $metaLine['value'] . "'");
                        break;
                }
            }
            if (count($key) != 0 && count($value) != 0) {
                array_push($key, '`product_id`');
                array_push($value, $line['product_id']);
                array_push($key, '`product_item_id`');
                array_push($value, $line['id']);
                $sql = $sql . ' (' . implode(',', $key) . ')' . 'values (' . implode(',', $value) . ')';
                if (! mysql_query($sql)){
                     //$str_result .= "<span style='color:#f00'>{$sql}</span><br />\n";
                     echo $sql;
                     exit();
                }
                else{
                    //$str_result .= $sql . "<br />\n";
                }
            }
            echo ".";
        }
        mysql_free_result($metaResult);
        mysql_free_result($result);
        //
        //$file = 'd:\auto_metadata_create_log.htm';
        // $hf = fopen($file, 'w+');
        // fwrite($hf, $str_result, strlen($str_result));
        // fclose($hf);
        //echo 'Please check log file' . $file;
    }*/
    
    public function refreshTableData ($metadataSetIds)
    {
        if(empty($metadataSetIds)){
            exit();
        }
        $metadataSetId = implode(",",$metadataSetIds);
        //查找全部有效的 product_item
        $query = "SELECT `product_items`.*, `products`.`metadata_set_id` FROM `product_items` inner join `products` on `products`.id = `product_items`.product_id and `products`.is_delete = 0 where `product_items`.is_delete=0 and `products`.`metadata_set_id` in ({$metadataSetId}) ";
        $result = mysql_query($query) or die('Query failed: ' . mysql_error());
        while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
            //$sql = 'INSERT INTO ';
            $tableName = 'auto_metadata_set_' . $line['metadata_set_id'];
            //$sql = $sql . $tableName;
            //按 product_item_id 查找 该 product_item 的全部有效的 item_metadata
            $metaQuery = 'select    item_metadatas.*,metadatas.input_type
                        from    item_metadatas
                        inner   join metadatas on metadatas.id = item_metadatas.metadata_id
                        and     metadatas.is_delete = 0
                        where   item_metadatas.is_delete=0 and product_item_id = ' . $line['id'];
            $metaResult = mysql_query($metaQuery) or die('Query failed: ' . mysql_error());
            $key = array();
            $value = array();
            $multiMetadatas = array();
            $multiMetadataNames = array();
            $multiMetaValues = array();
            $items = null;
             //生成 insert 语句的数据部分 
            while ($metaLine = mysql_fetch_array($metaResult, MYSQL_ASSOC)) {
                $fieldName = 'auto_metadata_' . $metaLine['metadata_id'];
                switch ($metaLine['input_type']) {
                    case 2:
                        array_push($key, '`' . $fieldName . '`');
                        array_push($value, "'" . $metaLine['desc_value'] . "'");
                        break;
                    case 1:
                    case 3:
                    case 6:
                    case 5:
                        array_push($key, '`' . $fieldName . '`');
                        array_push($value, "'" . $metaLine['value'] . "'");
                        break;
                    case 4:
                        if(!in_array($fieldName,$multiMetadataNames)){
                            $multiMetadataNames[] = $fieldName;
                        }                        
                        $multiMetadatas[$metaLine['metadata_id']][] = $metaLine['value'];
                        break;
                }
            }
            //生成inpty type = 4 的组合数据
            $num = 1;
            $i = 0;
            foreach($multiMetadatas as $multiMetadata){
                $valueCount = sizeof($multiMetadata);
                $num *= $valueCount;
                $multiMetaValues[] =  array("index"=>$i,"value"=>$multiMetadata);
                $i++;
            }
            $repeatNum = $num;
            foreach($multiMetaValues as $multiMetaValue){
                $valueList = $multiMetaValue['value'];
                $index = $multiMetaValue['index'];
                $valueListCount = sizeof($valueList);
                $repeatNum = $repeatNum / $valueListCount;
                $k = 1;
                foreach($valueList as $valueObj){
                    $startPoint = $k;
                    $spaceCount = $num / $valueListCount / $repeatNum;
                    for($j=1; $j<=$spaceCount; $j++){
                        for($i=0; $i<$repeatNum; $i++){
                            $items[$startPoint+$i-1][$index] = $valueObj;
                        }
                        $startPoint += $repeatNum * $valueListCount;
                    }
                    $k += $repeatNum;                    
                }
            }
            //insert db
            if(!isset($items) || empty($items)){
                if (count($key) != 0 && count($value) != 0) {
                    array_push($key, '`product_id`');
                    array_push($value, $line['product_id']);
                    array_push($key, '`product_item_id`');
                    array_push($value, $line['id']);
                    $sql = 'INSERT INTO ';
                    $sql = $sql . $tableName;
                    $sql = $sql . ' (' . implode(',', $key) . ')' . 'values (' . implode(',', $value) . ')';
                    if (! mysql_query($sql)){
                         //$str_result .= "<span style='color:#f00'>{$sql}</span><br />\n";
                         echo $sql;
                         exit();
                    }
                }                
            }else{
                foreach($items as $item){
                    $tempKeys = $key;
                    $tempValues = $value;
                    array_push($tempKeys, '`product_id`');
                    array_push($tempValues, $line['product_id']);
                    array_push($tempKeys, '`product_item_id`');
                    array_push($tempValues, $line['id']);
                    foreach($item as $index => $tempItem){
                        array_push($tempKeys, '`'.$multiMetadataNames[$index].'`');
                        array_push($tempValues, $tempItem);
                    }
                    $sql = 'INSERT INTO ';
                    $sql = $sql . $tableName;
                    $sql = $sql . ' (' . implode(',', $tempKeys) . ')' . 'values (' . implode(',', $tempValues) . ')';
                    if (! mysql_query($sql)){
                         //$str_result .= "<span style='color:#f00'>{$sql}</span><br />\n";
                         echo $sql;
                         exit();
                    }                    
                }               
            }
            echo ".";
        }
        mysql_free_result($metaResult);
        mysql_free_result($result);
     }
}
$a = new MakeTable();
$a->refreshTable();